/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.reportMahasiswaLulusanReguler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 */
public class DataLulusanReguler {
    
   reportMahasiswaLulusanReguler mhsLulusanReguler;
    
    public List<reportMahasiswaLulusanReguler> daftarMhsLulusanReguler(){
        
        List<reportMahasiswaLulusanReguler> listMhslulusanregular = new ArrayList<reportMahasiswaLulusanReguler>();
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT kuota.TAHUN_AJARAN, kuota.KUOTA_MHS, jumlah_calon_mahasiswa.CALON_MHS_IKUT, jumlah_calon_mahasiswa.CALON_MHS_LULUS "
                + "FROM jumlah_calon_mahasiswa, kuota "
                + "WHERE kuota.ID_TAHUN_AJARAN = jumlah_calon_mahasiswa.TAHUN_AJARAN GROUP BY kuota.TAHUN_AJARAN";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                String tahun = rs.getString("TAHUN_AJARAN");
                
                this.mhsLulusanReguler = new reportMahasiswaLulusanReguler();
                mhsLulusanReguler.setTotalLulusanRegularBukanTransfer(rs.getInt("TAHUN_AJARAN"));
                mhsLulusanReguler.setTotalLulusanTransfer(rs.getInt("KUOTA_MHS"));
                mhsLulusanReguler.setIpkMinLulusan(rs.getInt("CALON_MHS_IKUT"));
                mhsLulusanReguler.setIpkRatLulusan(rs.getInt("CALON_MHS_LULUS"));
                mhsLulusanReguler.setIpkMakLulusan(rs.getInt("CALON_MHS_LULUS"));
              //  mhsLulusanReguler.setIpkKurangDariDua(getBaruRequlerNonTransfer(tahun));
               // mhsLulusanReguler.setIpkAntaraDuaTiga(getBaruTransfer(tahun));
               // mhsLulusanReguler.setIpkLebihDariTiga(getTotalRequlerNonTransfer(tahun)-getTotalLulusRequlerNonTransfer(tahun));
                 listMhslulusanregular.add(mhsLulusanReguler);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return listMhslulusanregular;
        
    }
    
    public String getBaruRequlerNonTransfer(String tahun){
        String baruRegulerNonTransfer = "-";
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN = '"+tahun+"' AND STATUS_TRANSFER = '0' AND STATUS_REGULER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                baruRegulerNonTransfer = rs.getString("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return baruRegulerNonTransfer;
    }
    
    public String getBaruTransfer(String tahun){
        String baruTransfer = "-";
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN = '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                baruTransfer = rs.getString("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return baruTransfer;
    }
    
    /*SELECT COUNT( ID_USER ) FROM mahasiswa WHERE TAHUN_ANGKATAN <=2010*/
    
    public int getTotalRequlerNonTransfer(String tahun){
        int totalNonTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) FROM mahasiswa WHERE TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '0' AND STATUS_REGULER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalNonTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalNonTransfer;
    }
    
    public int getTotalLulusRequlerNonTransfer(String tahun){
        int totalLulus = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "select count(a.id_user) from mahasiswa as a, lulusan as b where a.id_user=b.id_user and status_reguler='1' and STATUS_TRANSFER = '0' tahun_angkatan<='"+tahun+"'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalLulus = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalLulus;
    }
    
    public int getTotalTransfer(String tahun){
        int totalTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalTransfer;
    }
    
    public int getTotalLulusTransfer(String tahun){
        int totalTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT count(a.id_user) from mahasiswa as a, lulusan as b where a.id_user=b.id_user and TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataLulusanReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalTransfer;
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}
